<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "https://www.w3.org/TR/html4/loose.dtd">
<html lang="zh-CN"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta http-equiv="Content-Language" content="zh-CN"><link href="stylesheet.css" media="all" rel="stylesheet" type="text/css">
<title>二进制字符串函数和操作符</title>
<script> var _hmt = _hmt || []; (function() { var hm = document.createElement("script"); hm.src = "https://hm.baidu.com/hm.js?d286c55b63a3c54a1e43d10d4c203e75"; var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(hm, s); })(); </script>
</head><body class="SECT1">
<div>
<table summary="Header navigation table" width="100%" border="0" cellpadding="0" cellspacing="0">
<tr><th colspan="5" align="center" valign="bottom">PostgreSQL 8.2.3 中文文档</th></tr>
<tr><td width="10%" align="left" valign="top"><a href="functions-string.html" accesskey="P">后退</a></td><td width="10%" align="left" valign="top"><a href="functions.html">快退</a></td><td width="60%" align="center" valign="bottom">章9. 函数和操作符</td><td width="10%" align="right" valign="top"><a href="functions.html">快进</a></td><td width="10%" align="right" valign="top"><a href="functions-bitstring.html" accesskey="N">前进</a></td></tr>
</table>
<hr align="LEFT" width="100%"></div>
<div class="SECT1"><h1 class="SECT1"><a name="FUNCTIONS-BINARYSTRING">9.5. 二进制字符串函数和操作符</a></h1><a name="AEN8790"></a>
<p>本节描述那些检查和操作类型为 <tt class="TYPE">bytea</tt> 数值的函数和操作符。</p>
<p>SQL 定义了一些有特殊语法的字符串函数，在这些函数里使用特殊关键字而不是逗号来分隔参数。详情请见<a href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-SQL">表9-8</a>。一些函数也实现了使用常用语法进行函数调用的方法(参阅<a href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-OTHER">表9-9</a>)。</p>
<div class="TABLE"><a name="FUNCTIONS-BINARYSTRING-SQL"></a>
<p><b>表9-8. SQL 二进制字符串函数和操作符</b></p>
<table border="1" class="CALSTABLE"><col><col><col><col><col>
<thead>
<tr><th>函数</th><th>返回类型</th><th>描述</th><th>例子</th><th>结果</th></tr>
</thead>
<tbody>
<tr><td><tt class="LITERAL"><tt class="PARAMETER">string</tt> <tt class="LITERAL">||</tt> <tt class="PARAMETER">string</tt></tt></td><td><tt class="TYPE">bytea</tt></td><td>字符串连接<a name="AEN8820"></a></td><td><tt class="LITERAL">E'\\\\Post'::bytea || E'\\047gres\\000'::bytea</tt></td><td><tt class="LITERAL">\\Post'gres\000</tt></td></tr>
<tr><td><code class="FUNCTION">get_bit</code>(<tt class="PARAMETER">string</tt>, <tt class="PARAMETER">offset</tt>)</td><td><tt class="TYPE">int</tt></td><td>从字符串中抽取位<a name="AEN8835"></a></td><td><tt class="LITERAL">get_bit(E'Th\\000omas'::bytea, 45)</tt></td><td><tt class="LITERAL">1</tt></td></tr>
<tr><td><code class="FUNCTION">get_byte</code>(<tt class="PARAMETER">string</tt>, <tt class="PARAMETER">offset</tt>)</td><td><tt class="TYPE">int</tt></td><td>从字符串中抽取字节<a name="AEN8849"></a></td><td><tt class="LITERAL">get_byte(E'Th\\000omas'::bytea, 4)</tt></td><td><tt class="LITERAL">109</tt></td></tr>
<tr><td><tt class="LITERAL"><code class="FUNCTION">octet_length</code>(<tt class="PARAMETER">string</tt>)</tt></td><td><tt class="TYPE">int</tt></td><td>二进制字符串中的字节数</td><td><tt class="LITERAL">octet_length( E'jo\\000se'::bytea)</tt></td><td><tt class="LITERAL">5</tt></td></tr>
<tr><td><tt class="LITERAL"><code class="FUNCTION">position</code>(<tt class="PARAMETER">substring</tt> in <tt class="PARAMETER">string</tt>)</tt></td><td><tt class="TYPE">int</tt></td><td>特定子字符串的位置</td><td><tt class="LITERAL">position(E'\\000om'::bytea in E'Th\\000omas'::bytea)</tt></td><td><tt class="LITERAL">3</tt></td></tr>
<tr><td><code class="FUNCTION">set_bit</code>(<tt class="PARAMETER">string</tt>, <tt class="PARAMETER">offset</tt>, <tt class="PARAMETER">newvalue</tt>)</td><td><tt class="TYPE">bytea</tt></td><td>设置字符串中的位<a name="AEN8889"></a></td><td><tt class="LITERAL">set_bit(E'Th\\000omas'::bytea, 45, 0)</tt></td><td><tt class="LITERAL">Th\000omAs</tt></td></tr>
<tr><td><code class="FUNCTION">set_byte</code>(<tt class="PARAMETER">string</tt>, <tt class="PARAMETER">offset</tt>, <tt class="PARAMETER">newvalue</tt>)</td><td><tt class="TYPE">bytea</tt></td><td>设置字符串中的字节<a name="AEN8904"></a></td><td><tt class="LITERAL">set_byte(E'Th\\000omas'::bytea, 4, 64)</tt></td><td><tt class="LITERAL">Th\000o@as</tt></td></tr>
<tr><td><tt class="LITERAL"><code class="FUNCTION">substring</code>(<tt class="PARAMETER">string</tt> [<span class="OPTIONAL">from <tt class="TYPE">int</tt></span>] [<span class="OPTIONAL">for <tt class="TYPE">int</tt></span>])</tt></td><td><tt class="TYPE">bytea</tt></td><td>抽取子字符串<a name="AEN8922"></a></td><td><tt class="LITERAL">substring(E'Th\\000omas'::bytea from 2 for 3)</tt></td><td><tt class="LITERAL">h\000o</tt></td></tr>
<tr><td><tt class="LITERAL"><code class="FUNCTION">trim</code>([<span class="OPTIONAL">both</span>] <tt class="PARAMETER">bytes</tt> from <tt class="PARAMETER">string</tt>)</tt></td><td><tt class="TYPE">bytea</tt></td><td>从 <tt class="PARAMETER">string</tt> 的开头和结尾删除只包含 <tt class="PARAMETER">bytes</tt> 中字节的最长字符串</td><td><tt class="LITERAL">trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea)</tt></td><td><tt class="LITERAL">Tom</tt></td></tr>
</tbody>
</table>
</div>
<p>还有一些二进制字符串处理函数可以使用，在<a href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-OTHER">表9-9</a>列出。其中有一些是在内部使用，用于实现<a href="functions-binarystring.html#FUNCTIONS-BINARYSTRING-SQL">表9-8</a>列出的 SQL 标准的字符串函数。</p>
<div class="TABLE"><a name="FUNCTIONS-BINARYSTRING-OTHER"></a>
<p><b>表9-9. 其它二进制字符串函数</b></p>
<table border="1" class="CALSTABLE"><col><col><col><col><col>
<thead>
<tr><th>函数</th><th>返回类型</th><th>描述</th><th>例子</th><th>结果</th></tr>
</thead>
<tbody>
<tr><td><tt class="LITERAL"><code class="FUNCTION">btrim</code>(<tt class="PARAMETER">string</tt> <tt class="TYPE">bytea</tt>, <tt class="PARAMETER">bytes</tt> <tt class="TYPE">bytea</tt>)</tt></td><td><tt class="TYPE">bytea</tt></td><td>从 <tt class="PARAMETER">string</tt> 的开头和结尾删除只包含 <tt class="PARAMETER">bytes</tt> 中字节的最长的字符串</td><td><tt class="LITERAL">btrim(E'\\000trim\\000'::bytea, E'\\000'::bytea)</tt></td><td><tt class="LITERAL">trim</tt></td></tr>
<tr><td><tt class="LITERAL"><code class="FUNCTION">decode</code>(<tt class="PARAMETER">string</tt> <tt class="TYPE">text</tt>, <tt class="PARAMETER">type</tt> <tt class="TYPE">text</tt>)</tt></td><td><tt class="TYPE">bytea</tt></td><td>把之前用 <code class="FUNCTION">encode</code> 编码的结果 <tt class="PARAMETER">string</tt> 解码为二进制字符串。参数类型和 <code class="FUNCTION">encode</code> 相同。</td><td><tt class="LITERAL">decode(E'123\\000456', 'escape')</tt></td><td><tt class="LITERAL">123\000456</tt></td></tr>
<tr><td><tt class="LITERAL"><code class="FUNCTION">encode</code>(<tt class="PARAMETER">string</tt> <tt class="TYPE">bytea</tt>, <tt class="PARAMETER">type</tt> <tt class="TYPE">text</tt>)</tt></td><td><tt class="TYPE">text</tt></td><td>把二进制字符串编码为只包含 ASCII 的表现形式。支持：<tt class="LITERAL">base64</tt>, <tt class="LITERAL">hex</tt>, <tt class="LITERAL">escape</tt></td><td><tt class="LITERAL">encode(E'123\\000456'::bytea, 'escape')</tt></td><td><tt class="LITERAL">123\000456</tt></td></tr>
<tr><td><tt class="LITERAL"><code class="FUNCTION">length</code>(<tt class="PARAMETER">string</tt>)</tt></td><td><tt class="TYPE">int</tt></td><td>二进制字符串的长度<a name="AEN9021"></a><a name="AEN9024"></a></td><td><tt class="LITERAL">length(E'jo\\000se'::bytea)</tt></td><td><tt class="LITERAL">5</tt></td></tr>
<tr><td><tt class="LITERAL"><code class="FUNCTION">md5</code>(<tt class="PARAMETER">string</tt>)</tt></td><td><tt class="TYPE">text</tt></td><td>计算 <tt class="PARAMETER">string</tt> 的MD5散列值，以十六进制方式返回结果。</td><td><tt class="LITERAL">md5(E'Th\\000omas'::bytea)</tt></td><td><tt class="LITERAL">8ab2d3c9689aaf18 b4958c334c82d8b1</tt></td></tr>
</tbody>
</table>
</div>
</div>
<div>
<hr align="LEFT" width="100%">
<table summary="Footer navigation table" width="100%" border="0" cellpadding="0" cellspacing="0">
<tr><td width="33%" align="left" valign="top"><a href="functions-string.html" accesskey="P">后退</a></td><td width="34%" align="center" valign="top"><a href="index.html" accesskey="H">首页</a></td><td width="33%" align="right" valign="top"><a href="functions-bitstring.html" accesskey="N">前进</a></td></tr>
<tr><td width="33%" align="left" valign="top">字符串函数和操作符</td><td width="34%" align="center" valign="top"><a href="functions.html" accesskey="U">上一级</a></td><td width="33%" align="right" valign="top">位串函数和操作符</td></tr>
</table>
</div>
</body></html>